package mww.javabean.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import mww.common.*;

import mww.javabean.entity.Teacher;

public class TeacherDaoImpl implements TeacherDao {
	// 添加教师方法
	@Override
	public void addTeacher(Teacher teacher) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "insert into Teacher values(?,?,?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, teacher.getTeacherNo());
			pstmt.setString(2, teacher.getTeacherName());
			pstmt.setString(3, teacher.getPassword());
			pstmt.setInt(4, teacher.getProfessional());
			pstmt.setString(5, teacher.getEducation());
			pstmt.setString(6, teacher.getAddress());
			pstmt.setString(7, teacher.getPhone());
			pstmt.setString(8, teacher.getEmail());
			pstmt.setString(9, teacher.getSubject());
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
	}

	// 删除教师方法
	@Override
	public void delTeacher(String teacherNo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "delete from Teacher where teacherNo=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, teacherNo);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
	}

	// 修改教师信息
	@Override
	public void updateTeacher(Teacher teacher) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "update Teacher set teacherNo=?,teacherName=?,password=?,professional=?,"
					+ "education=?,address=?,phone=?,email=?,subject=? where teacherNo=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, teacher.getTeacherNo());
			pstmt.setString(2, teacher.getTeacherName());
			pstmt.setString(3, teacher.getPassword());
			pstmt.setInt(4, teacher.getProfessional());
			pstmt.setString(5, teacher.getEducation());
			pstmt.setString(6, teacher.getAddress());
			pstmt.setString(7, teacher.getPhone());
			pstmt.setString(8, teacher.getEmail());
			pstmt.setString(9, teacher.getSubject());
			pstmt.setString(10, teacher.getTeacherNo());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
	}

	// 根据教师编号查找教师
	@Override
	public Teacher findTeacherByTeacherNo(String teacherNo) {
		Teacher teacher = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "select * from Teacher where teacherNo=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, teacherNo);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				teacher = new Teacher();
				teacher.setTeacherNo(rs.getString(1));
				teacher.setTeacherName(rs.getString(2));
				teacher.setPassword(rs.getString(3));
				teacher.setProfessional(rs.getInt(4));
				teacher.setEducation(rs.getString(5));
				teacher.setAddress(rs.getString(6));
				teacher.setPhone(rs.getString(7));
				teacher.setEmail(rs.getString(8));
				teacher.setSubject(rs.getString(9));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return teacher;
	}

	// 列表显示所有教师列表--分页
	@Override
	public Map findAllTeacher(int curPage) {
		Teacher teacher = null;
		ArrayList list = new ArrayList();
		Connection conn = null;
		Statement pstmt = null;
		ResultSet rs = null;
		ResultSet r = null;
		Map map = null;
		Page pa = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "select * from Teacher order by teacherNo";
			pstmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = pstmt.executeQuery(sql);
			pa = new Page();// 声明分页类对象
			pa.setPageSize(5);
			pa.setPageCount(rs);
			pa.setCurPage(curPage);
			r = pa.setRs(rs);
			r.previous();
			for (int i = 0; i < pa.getPageSize(); i++) {
				if (r.next()) {
					teacher = new Teacher();
					teacher.setTeacherNo(r.getString(1));
					teacher.setTeacherName(r.getString(2));
					teacher.setPassword(rs.getString(3));
					teacher.setProfessional(r.getInt(4));
					teacher.setEducation(r.getString(5));
					teacher.setAddress(r.getString(6));
					teacher.setPhone(r.getString(7));
					teacher.setEmail(r.getString(8));
					teacher.setSubject(r.getString(9));
					list.add(teacher);
				} else {
					break;
				}
			}
			map = new HashMap();
			map.put("list", list);
			map.put("pa", pa);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
			ResourceClose.close(r, null, null);
		}
		return map;
	}

	// 列表显示所有教师列表
	@Override
	public List findAllTeacher() {
		Teacher teacher = null;
		ArrayList list = new ArrayList();
		Connection conn = null;
		Statement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "select * from Teacher order by teacherNo";
			pstmt = conn.createStatement();
			rs = pstmt.executeQuery(sql);
			while (rs.next()) {
				teacher = new Teacher();
				teacher.setTeacherNo(rs.getString(1));
				teacher.setTeacherName(rs.getString(2));
				teacher.setPassword(rs.getString(3));
				teacher.setProfessional(rs.getInt(4));
				teacher.setEducation(rs.getString(5));
				teacher.setAddress(rs.getString(6));
				teacher.setPhone(rs.getString(7));
				teacher.setEmail(rs.getString(8));
				teacher.setSubject(rs.getString(9));
				list.add(teacher);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return list;
	}

	// 多条件查询教师
	@Override
	public List findAllTeacherByMostCon(Teacher condition) {
		Teacher teacher = null;
		ArrayList list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		String teacherNo = condition.getTeacherNo();
		String teacherName = condition.getTeacherName();
		String phone = condition.getPhone();
		String subject = condition.getSubject();
		Integer professional = condition.getProfessional();

		// 构造多条件查询的SQL语句
		String sql = "select * from Teacher where 1=1 ";

		// 模糊查询
		if (teacherNo != null && !teacherNo.equals("")) {
			sql += " and teacherNo like '%" + teacherNo + "%'";
		}
		if (teacherName != null && !teacherName.equals("")) {
			sql += " and teacherName like '%" + teacherName + "%'";
		}
		if (phone != null && !phone.equals("")) {
			sql += " and phone like '%" + phone + "%'";
		}
		if (subject != null && !subject.equals("")) {
			sql += " and subject like '%" + subject + "%'";
		}
		if (professional != null && !professional.equals("")) {
			sql += " and professional=" + professional;
		}

		sql += " order by teacherNo";
		try {
			conn = ConnectionFactory.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				teacher = new Teacher();
				teacher.setTeacherNo(rs.getString(1));
				teacher.setTeacherName(rs.getString(2));
				teacher.setPassword(rs.getString(3));
				teacher.setProfessional(rs.getInt(4));
				teacher.setEducation(rs.getString(5));
				teacher.setAddress(rs.getString(6));
				teacher.setPhone(rs.getString(7));
				teacher.setEmail(rs.getString(8));
				teacher.setSubject(rs.getString(9));
				list.add(teacher);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return list;
	}

	// 多条件查询教师-带分页
	@Override
	public Map findAllTeacherByMostCon(Teacher condition, int curPage) {
		Teacher teacher = null;
		ArrayList list = new ArrayList();
		Connection conn = null;
		Statement pstmt = null;
		ResultSet rs = null;
		ResultSet r = null;
		Map map = null;
		Page pa = null;
		String teacherNo = condition.getTeacherNo();
		String teacherName = condition.getTeacherName();
		String phone = condition.getPhone();
		String subject = condition.getSubject();
		Integer professional = condition.getProfessional();

		// 构造多条件查询的SQL语句
		String sql = "select * from Teacher where 1=1 ";
		// 模糊查询
		if (teacherNo != null && !teacherNo.equals("")) {
			sql += " and teacherNo like '%" + teacherNo + "%'";
		}
		if (teacherName != null && !teacherName.equals("")) {
			sql += " and teacherName like '%" + teacherName + "%'";
		}
		if (phone != null && !phone.equals("")) {
			sql += " and phone like '%" + phone + "%'";
		}
		if (subject != null && !subject.equals("")) {
			sql += " and subject like '%" + subject + "%'";
		}
		if (professional != null && !professional.equals("")) {
			sql += " and professional=" + professional;
		}

		sql += " order by teacherNo";
		try {
			conn = ConnectionFactory.getConnection();
			pstmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = pstmt.executeQuery(sql);
			pa = new Page();// 声明分页类对象
			pa.setPageSize(5);
			pa.setPageCount(rs);
			pa.setCurPage(curPage);
			r = pa.setRs(rs);
			r.previous();
			for (int i = 0; i < pa.getPageSize(); i++) {
				if (rs.next()) {
					teacher = new Teacher();
					teacher.setTeacherNo(rs.getString(1));
					teacher.setTeacherName(rs.getString(2));
					teacher.setPassword(rs.getString(3));
					teacher.setProfessional(rs.getInt(4));
					teacher.setEducation(rs.getString(5));
					teacher.setAddress(rs.getString(6));
					teacher.setPhone(rs.getString(7));
					teacher.setEmail(rs.getString(8));
					teacher.setSubject(rs.getString(9));
					list.add(teacher);
				} else {
					break;
				}
			}
			map = new HashMap();
			map.put("list", list);
			map.put("pa", pa);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
			ResourceClose.close(r, null, null);
		}
		return map;
	}

	@Override
	public void changePwd(String loginName, String newPassword) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "update Teacher set password=? where teacherNo=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, newPassword);
			pstmt.setString(2, loginName);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
	}
}
